/*
* Mentioned DB1 as your Compared Database here
*/
;WITH 
SrcObj AS (
              SELECT o.Name AS ObjName, o.Object_id
              FROM   mainserver.DB1.sys.Objects o
          ),
SrcCols AS (
               SELECT c.Object_id, o.Name AS TableName, c.Column_id, C.Name AS ColName
               FROM   mainserver.DB1.sys.Columns c
                      INNER JOIN mainserver.DB1.sys.Tables o
                           ON  o.OBJECT_ID = c.OBJECT_ID
           ),
Srcindex AS (
                SELECT i.[object_id], o.Name AS ObjName, i.index_id, i.name, i.type, i.type_desc, i.is_unique, 
                       i. is_primary_key, i.is_unique_constraint,(SELECT count(*)FROM mainserver.DB1.sys.index_columns ic WHERE ic.index_id =i.index_id) AS ColCount
                FROM   mainserver.DB1.sys.indexes i
                       INNER JOIN mainserver.DB1.sys.objects o
                            ON  o.[object_id] = i.[object_id]
                WHERE  i.[type] <> 0 AND o.[type] = 'U' AND i.is_primary_key = 0 AND o.name <>'sysdiagrams'
            ),
SrcindexCols AS (
                    SELECT i.name, i.type_desc, i.is_unique, ic.*, Keycols.ColName AS KeycolName, 
                           IncCols.colName incColNAme, i.ObjName
                    FROM   SrcIndex i
                           INNER JOIN mainserver.DB1.sys.index_columns ic
                                ON  ic.[object_id] = i.[object_id] AND ic.index_id = i.index_id
                           LEFT JOIN Srccols Keycols
                                ON  keycols.[object_id] = ic.[object_id] AND Keycols.Column_id = ic.column_id AND 
                                    ic.is_included_column = 0
                           LEFT JOIN Srccols IncCols
                                ON  inccols.[object_id] = ic.[object_id] AND IncCols.Column_id = ic.column_id AND 
                                    ic.is_included_column = 1
                ),
DesCols AS (
               SELECT c.Object_id, o.Name AS TableName, c.Column_id, C.Name AS ColName
               FROM   sys.Columns c
                      INNER JOIN sys.Tables o
                           ON  o.OBJECT_ID = c.OBJECT_ID
           ),
Desindex AS (
                SELECT i.[object_id], o.Name AS ObjName, i.index_id, i.name, i.type_desc, i.is_unique, 
                       i. is_primary_key, i.is_unique_constraint,(SELECT count(*)FROM sys.index_columns ic WHERE ic.index_id =i.index_id) AS ColCount
                FROM   sys.indexes i
                       INNER JOIN sys.objects o
                            ON  o.[object_id] = i.[object_id]
                WHERE  i.[type] <> 0 AND o.[type] = 'U' AND i.is_primary_key = 0 AND o.name <>'sysdiagrams'
            ),
DesindexCols AS (
                    SELECT i.name, i.type_desc, i.is_unique, ic.*, Keycols.ColName AS KeycolName, 
                           IncCols.colName incColNAme
                    FROM   Desindex I
                           INNER JOIN sys.index_columns ic
                                ON  ic.[object_id] = i.[object_id] AND ic.index_id = i.index_id
                           LEFT JOIN Descols Keycols
                                ON  keycols.[object_id] = ic.[object_id] AND Keycols.Column_id = ic.column_id AND 
                                    ic.is_included_column = 0
                           LEFT JOIN descols IncCols
                                ON  inccols.[object_id] = ic.[object_id] AND IncCols.Column_id = ic.column_id AND 
                                    ic.is_included_column = 1
                )           
SELECT 'IF EXISTS(SELECT * FROM sys.indexes i WHERE i.name ='''+s.Name+''')
	Drop INDEX '+s.Name+' ON ' + s.ObjName + '
GO
CREATE' + CASE 
                        WHEN s.is_unique = 1 THEN ' UNIQUE'
                        ELSE ''
                   END + ' ' + s.Type_desc + ' INDEX ' + s.Name + ' 
ON ' + s.ObjName + '(' + STUFF(
           (
               SELECT ',' + idx.KeycolName
               FROM   srcindexcols Idx
               WHERE  idx.index_id = s.Index_id AND idx.Object_id = s.Object_id AND idx.incColName IS NULL
               ORDER BY
                      idx.index_column_id ASC
                      FOR XML PATH ('')
           ), 1, 1, ''
       )
       + ')' + ISNULL(
           ' 
INCLUDE (' +
           STUFF(
               (
                   SELECT ',' + idx.IncColName
                   FROM   srcindexcols Idx
                   WHERE  idx.index_id = s.Index_id AND idx.Object_id = s.Object_id AND idx.incColName IS NOT NULL
                   ORDER BY
                          idx.index_column_id ASC
                          FOR XML PATH ('')
               ), 1, 1, ''
           )
           +
           ') ', ''
       ) + '
GO --' +CAST (s.Index_id AS VARCHAR)+' , '+ CAST (s.object_id AS VARCHAR)
       COLLATE DATABASE_DEFAULT
FROM   SrcIndex s
       LEFT OUTER JOIN desIndex d
            ON  s.Name = d.NAME AND s.colcount=d.colcount
WHERE  d.Name IS NULL AND NOT EXISTS(
           SELECT sc.keycolName,sc.inccolName,dc.ColName,Dc.Tablename
           FROM   SrcindexCols sc LEFT JOIN descols dc ON
           sc.Objname  =dc.TableName AND (sc.KeycolName =dc.ColName OR sc.IncColName=dc.Colname)
           WHERE sc.index_id = s.index_id 
           AND Dc.ColName IS NULL           
)


















/*

SELECT 
'CREATE '+
    CASE WHEN sys.indexes.is_unique = 1 AND sys.indexes.is_primary_key = 0 THEN 'UNIQUE ' ELSE '' END +
    CASE WHEN sys.indexes.type_desc = 'CLUSTERED' THEN 'CLUSTERED ' ELSE 'NONCLUSTERED ' END
+'INDEX '
+ sys.indexes.name
+ ' ON ' 
+ sys.objects.name
+ ' ( ' + Index_Columns.index_columns_key +' ) '
+ ISNULL('INCLUDE ('+Index_Columns.index_columns_include+')','')
FROM
    sys.objects
    JOIN sys.schemas ON sys.objects.schema_id=sys.schemas.schema_id
    JOIN sys.indexes ON sys.indexes.object_id=sys.objects.object_id
    CROSS APPLY (
       SELECT
         LEFT(index_columns_key, LEN(index_columns_key)-1) AS index_columns_key,
         LEFT(index_columns_include, LEN(index_columns_include)-1) AS index_columns_include
       FROM
         (
          SELECT
              (
                 SELECT sys.columns.name + ','
                 FROM
                   sys.index_columns
                   JOIN sys.columns ON
                    sys.index_columns.column_id=sys.columns.column_id
                    AND sys.index_columns.object_id=sys.columns.object_id
                 WHERE
                   sys.index_columns.is_included_column=0
                   AND sys.indexes.object_id=sys.index_columns.object_id AND sys.indexes.index_id=sys.index_columns.index_id
                 ORDER BY key_ordinal
                 FOR XML PATH('')
              ) AS index_columns_key,
              (
                 SELECT sys.columns.name + ','
                 FROM
                   sys.index_columns
                   JOIN sys.columns ON
                    sys.index_columns.column_id=sys.columns.column_id
                    AND sys.index_columns.object_id=sys.columns.object_id
                 WHERE
                   sys.index_columns.is_included_column=1
                   AND sys.indexes.object_id=sys.index_columns.object_id AND sys.indexes.index_id=sys.index_columns.index_id
                 ORDER BY index_column_id
                 FOR XML PATH('')
              ) AS index_columns_include
         ) AS Index_Columns
    ) AS Index_Columns
WHERE
    sys.objects.type='u'
    AND sys.objects.is_ms_shipped=0
    AND sys.indexes.type_desc <> 'HEAP'
    AND sys.indexes.is_primary_key = 0

*/  


